﻿using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;

[assembly: CLSCompliant(true)]
namespace JsonToSql
{
    /// <summary>
    /// Provider of json to SQL convertion and insert, update operation
    /// Prerequisities: http://www.microsoft.com/en-us/download/details.aspx?id=42295
    /// </summary>
    public class JsonToSqlProvider
    {
        private JsonRequest jsonRequest;
        /// <summary>
        /// Json provider constructor
        /// </summary>
        /// <param name="json">text of json format</param>
        public JsonToSqlProvider(string json)
        {
            jsonRequest = new JsonRequest(json);



        }
        /// <summary>
        /// Commit changes defined in json format
        /// </summary>
        public string Commit()
        {
            return Commit(ConfigurationManager.ConnectionStrings["JsonToSQL.Properties.Settings.JsonToSQLConnectionString"].ConnectionString);
        }
        /// <summary>
        ///  Commit changes defined in json format
        /// </summary>
        /// <param name="connectionString">connection string to DB</param>
        public string Commit(string connectionString)
        {
            string result = null;
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                Database db = createConnectionToDB(con);
                if (db == null)
                    return null;
                for (int i = 0; i < jsonRequest.Tables.Count; i++)
                {
                    prepareTable(jsonRequest.Tables[i], db);
                }
                for (int i = 0; i < jsonRequest.Tables.Count; i++)
                {
                    prepareRelations(jsonRequest.Tables[i], db);
                }

                for (int i = 0; i < jsonRequest.Tables.Count; i++)
                {
                    string cmdText = createIUCommand(jsonRequest.Tables[i]);
                    {
                        for (int j = 0; j < jsonRequest.Tables[i].Rows.Count; j++)
                        {
                            executeIUCommand(jsonRequest.Tables[i].Rows[j], con, cmdText);
                        }
                    }
                }
                for (int i = 0; i < jsonRequest.TablesToDelete.Count; i++)
                {
                    executeDeleteCommand(con, jsonRequest.TablesToDelete[i]);

                }

                for (int i = 0; i < jsonRequest.TablesToResult.Count; i++)
                {
                    string cmdResult = executeResultCommand(con, jsonRequest.TablesToResult[i]);
                    if (!string.IsNullOrEmpty(cmdResult))
                    {
                        result += (result == null ? "" : (", " + System.Environment.NewLine)) + cmdResult;
                    }
                }
                if (result != null)
                {
                    result = "{" + result + "}";
                }
            }
            return result;
        }
        #region SMO execution
        private static Database createConnectionToDB(SqlConnection con)
        {
            if (con == null)
                return null;
            ServerConnection sc = new ServerConnection(con);
            Server server = new Server(sc);
            Database db = server.Databases[con.Database];
            if (db == null)
            {

                Server forCreation = new Server(con.DataSource);
                db = new Database(forCreation, con.Database);
                db.Create();

                Exception exRefresh = null;
                int tryCycle = 0;
                do
                {
                    try
                    {
                        server.Databases.Refresh();
                        db = server.Databases[con.Database];
                        tryCycle++;
                    }
                    catch (SmoException ex)
                    {
                        System.Threading.Thread.Sleep(2000);
                        exRefresh = ex;
                    }
                }
                while (exRefresh != null && tryCycle < 10);

            }


            return db;
        }
        private static void prepareTable(JsonTable table, Database db)
        {
            if (table == null || db == null)
                return;

            Table tbl = null;
            bool existTable = false;
            if (!db.Tables.Contains(table.Name))
            {
                tbl = new Table(db, table.Name);
            }
            else
            {
                tbl = db.Tables[table.Name];
                existTable = true;
            }

            for (int i = 0; i < table.CreationColumns.Count; i++)
            {
                createColumn(tbl, table.CreationColumns[i].Name, table.CreationColumns[i].MaxLength,
                    table.CreationColumns[i].DataType, table.CreationColumns[i].PrimaryKey);
            }
            if (existTable)
            {
                tbl.Alter();
            }
            else
            {
                tbl.Create();
            }


        }

        private static Column createColumn(Table tbl, string name, int? maxLength,
            SqlDataType dataType, bool primaryKey)
        {
            Column col = null;
            bool existColumn = false;
            if (!tbl.Columns.Contains(name))
            {
                col = new Column(tbl, name);

            }
            else
            {
                col = tbl.Columns[name];
                existColumn = true;
            }
            if (maxLength != null)
            {
                col.DataType = new DataType(dataType, maxLength.Value);
            }
            else
            {
                col.DataType = new DataType(dataType);
            }

            if (primaryKey)
            {
                addPrimaryKey(tbl, col);
            }
            if (existColumn)
            {
                col.Alter();
            }
            else
            {
                tbl.Columns.Add(col);
            }
            return col;
        }
        private static void prepareRelations(JsonTable table, Database db)
        {
            if (table == null || db == null)
                return;
            Table tbl = null;
            tbl = db.Tables[table.Name];
            if (table.Entity.Relations != null)
            {
                for (int i = 0; i < table.Entity.Relations.Count; i++)
                {
                    switch (table.Entity.Relations[i].RelationType)
                    {
                        case "1:N":
                            {
                                createForeinKey(table.Entity.Relations[i].Name,
                                    table[table.Entity.Relations[i].Name].Keys,
                                    table.Entity.Relations[i].Keys, tbl);
                            }
                            break;

                        case "M:N":
                            {
                                createMNRelation(table, db, table.Entity.Relations[i]);
                            }
                            break;
                        case "N:M":
                            {
                                createMNRelation(table, db, table.Entity.Relations[i]);
                            }
                            break;
                    }
                }
            }
        }


        private static void createMNRelation(JsonTable primaryTable, Database db, JsonRelation relation)
        {
            Table tbl = null;
            string mnTableName = primaryTable.Name + relation.Name;
            bool existTable = false;
            if (!db.Tables.Contains(mnTableName))
            {
                tbl = new Table(db, mnTableName);
            }
            else
            {
                tbl = db.Tables[mnTableName];
                existTable = true;
            }
            List<Column> col1 = new List<Column>();
            for (int i = 0; i < primaryTable.Entity.Keys.Count; i++)
            {
                col1.Add(createColumn(tbl, primaryTable.Entity.Keys[i], null, primaryTable.Columns[primaryTable.Entity.Keys[i]].DataType, false));
            }
            JsonTable secondTable = primaryTable.GetTable(relation.Name);
            List<Column> col2 = new List<Column>();
            for (int i = 0; i < relation.Keys.Count; i++)
            {
                col2.Add(createColumn(tbl, relation.Keys[i], null, secondTable.Columns[secondTable.Entity.Keys[i]].DataType, false));
            }
            List<Column> pks = new List<Column>();
            pks.AddRange(col1);
            pks.AddRange(col2);
            addPrimaryKey(tbl, pks);
            if (existTable)
            {
                tbl.Alter();
            }
            else
            {
                tbl.Create();
            }
            List<string> col1Names = new List<string>((from item in col1
                                                       select item.Name).ToList());
            createForeinKey(primaryTable.Entity.Name, primaryTable.Entity.Keys, col1Names, tbl);
            List<string> col2Names = new List<string>((from item in col2
                                                       select item.Name).ToList());
            createForeinKey(secondTable.Entity.Name, secondTable.Entity.Keys, col2Names, tbl);
        }

        private static void createForeinKey(string pkTableName, List<string> pkKeys,
             List<string> fkKeys, Table tbl)
        {
            string postfix = "";
            for (int i = 0; i < fkKeys.Count; i++)
            {
                postfix += fkKeys[i];
            }
            string fkName = "FK_" + pkTableName + "_" + tbl.Name + "_" + postfix;
            if (!tbl.ForeignKeys.Contains(fkName))
            {
                ForeignKey fk = new ForeignKey(tbl, fkName);
                fk.DeleteAction = ForeignKeyAction.Cascade;
                fk.UpdateAction = ForeignKeyAction.Cascade;
                for (int i = 0; i < fkKeys.Count; i++)
                {
                    ForeignKeyColumn fkc = new ForeignKeyColumn(fk, fkKeys[i], pkKeys[i]);
                    fk.Columns.Add(fkc);
                    fk.ReferencedTable = pkTableName;
                    fk.ReferencedTableSchema = "dbo";
                    tbl.ForeignKeys.Add(fk);
                }
                fk.Create();
                tbl.Alter();
            }
        }
        private static void addPrimaryKey(Table table, Column col)
        {
            addPrimaryKey(table, new List<Column>() { col });
        }
        private static void addPrimaryKey(Table table, List<Column> cols)
        {

            if (!table.Indexes.Contains("PK_" + table.Name))
            {
                createPKIndex(table, cols);
            }
            else
            {
                Index idx = table.Indexes["PK_" + table.Name];
                for (int i = 0; i < cols.Count; i++)
                {
                    if (!idx.IndexedColumns.Contains(cols[i].Name))
                    {
                        idx.Drop();
                        createPKIndex(table, cols);
                        break; ;

                    }
                }

            }

        }

        private static IFormatProvider provider
        {
            get
            {
                return System.Threading.Thread.CurrentThread.CurrentUICulture;
            }
        }
        private static void createPKIndex(Table table, List<Column> cols)
        {
            Index idx = new Index(table, "PK_" + table.Name);
            table.Indexes.Add(idx);
            for (int i = 0; i < cols.Count; i++)
            {
                idx.IndexedColumns.Add(new IndexedColumn(idx, cols[i].Name));
            }
            idx.IsClustered = true;
            idx.IsUnique = true;
            idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
        }
        #endregion
        #region IUD rows
        private static string executeResultCommand(SqlConnection con, JsonTableResult table)
        {

            using (SqlCommand cmd = new SqlCommand(string.Format(CultureInfo.InvariantCulture, @"SELECT * FROM [{0}] t {1} ",
               table.Name, (table.Conditions == null || table.Conditions.Count == 0) ? "" : "where"), con))
            {

                for (int i = 0; i < table.Conditions.Count; i++)
                {
                    table.Conditions[i].AddCondition(cmd, i);
                }

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    return string.Format(CultureInfo.InvariantCulture, @"""{0}"":{1}", table.Name, serialize(dr));
                }
            }
        }
        private static void executeDeleteCommand(SqlConnection con, JsonTableDelete table)
        {
            using (SqlCommand cmd = new SqlCommand(string.Format(CultureInfo.InvariantCulture, @"DELETE t FROM [{0}] t {1} ",
                table.Name, (table.Conditions == null || table.Conditions.Count == 0) ? "" : "where"), con))
            {

                for (int i = 0; i < table.Conditions.Count; i++)
                {
                    table.Conditions[i].AddCondition(cmd, i);
                }

                cmd.ExecuteNonQuery();
            }
        }
        private static string createIUCommand(JsonTable table)
        {
            if (table == null)
                return null;
            string cmdText = @"
                    if(select count(*) from {4} t
                     where {0})=0
                    begin
		                    INSERT INTO {4}
		                    ({1})
		                    VALUES({2})
                    end
                    else
                    begin
		                    UPDATE t 
		                    SET 
		                    {3}
		                    FROM {4} t
		                    where {0}
                    end

                    SELECT {5} from {4} t
                    where {0}
                    ";
            string condition = null;
            for (int i = 0; i < table.PKColumns.Count; i++)
            {
                condition += condition == null ? "" : " AND ";
                condition += string.Format(provider, "t.{0}=@{0}", table.PKColumns[i].Name);
            }
            string columnNames = null;
            for (int i = 0; i < table.CreationColumns.Count; i++)
            {
                columnNames += columnNames == null ? "" : ", ";
                columnNames += string.Format(provider, "{0}", table.AllColumns[i].Name);
            }

            string paramNames = null;
            for (int i = 0; i < table.CreationColumns.Count; i++)
            {
                paramNames += paramNames == null ? "" : ", ";
                paramNames += string.Format(provider, "@{0}", table.AllColumns[i].Name);
            }
            string updateParamNames = null;
            for (int i = 0; i < table.ValueColumns.Count; i++)
            {
                updateParamNames += updateParamNames == null ? "" : ", ";
                updateParamNames += string.Format(provider, "{0}=@{0}", table.ValueColumns[i].Name);
            }
            string pkColumns = null;
            for (int i = 0; i < table.PKColumns.Count; i++)
            {
                pkColumns += pkColumns == null ? "" : ", ";
                pkColumns += string.Format(provider, "{0}", table.PKColumns[i].Name);
            }
            string result = string.Format(provider, cmdText, condition, columnNames, paramNames, updateParamNames, table.Name, pkColumns);
#if SaveResult
            System.IO.File.WriteAllText(table.Name, result);
#endif
            return result;
        }
        private static string createIURelationCommand(string tableName, List<string> keys)
        {
            if (keys == null)
                return null;
            string cmdText = @"
if(select count(*) from {3} t
 where {0})=0
begin
		INSERT INTO {3}
		({1})
		VALUES({2})
end

";
            string condition = null;
            for (int i = 0; i < keys.Count; i++)
            {
                condition += condition == null ? "" : " AND ";
                condition += string.Format(provider, "t.{0}=@{0}", keys[i]);
            }
            string columnNames = null;
            for (int i = 0; i < keys.Count; i++)
            {
                columnNames += columnNames == null ? "" : ", ";
                columnNames += string.Format(provider, "{0}", keys[i]);
            }

            string paramNames = null;
            for (int i = 0; i < keys.Count; i++)
            {
                paramNames += paramNames == null ? "" : ", ";
                paramNames += string.Format(provider, "@{0}", keys[i]);
            }


            string result = string.Format(provider, cmdText, condition, columnNames, paramNames, tableName);
#if SaveResult
            System.IO.File.WriteAllText(table.Name, result);
#endif
            return result;
        }
        private static void executeIUCommand(JsonRow row, SqlConnection conn, string cmdText)
        {
            if (row == null || conn == null || cmdText == null)
                return;

            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {

                for (int i = 0; i < row.Parent.CreationColumns.Count; i++)
                {

                    JsonCell cell = row[row.Parent.CreationColumns[i].Name];
                    cmd.Parameters.AddWithValue(cell.Name, cell.Value);

                }

                Dictionary<string, object> keyValues = new Dictionary<string, object>();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            keyValues.Add(dr.GetName(i), dr[i]);
                        }
                    }
                }
                if (row.Parent.NMRelations != null)
                {
                    for (int j = 0; j < row.Parent.NMRelations.Count; j++)
                    {
                        addRelationRows(row, row.Parent.NMRelations[j], conn, keyValues);
                    }
                }
            }
        }
        private static void addRelationRows(JsonRow row, JsonRelation relation, SqlConnection conn, Dictionary<string, object> pkValues)
        {
            string tableName = row.Parent.Name + relation.Name;
            List<string> keys = new List<string>();
            List<string> pkKeys = (from item in row.Parent.PKColumns
                                   select item.Name).ToList();
            keys.AddRange(pkKeys);
            keys.AddRange(relation.Keys);
            string cmdText = createIURelationCommand(tableName, keys);
            int index = 0;
            List<object> fkValues = getRelationData(row, relation.Keys, index);
            while (fkValues != null)
            {
                executeIURelationCommand(conn, cmdText, pkKeys, pkValues, relation.Keys, fkValues);
                index++;
                fkValues = getRelationData(row, relation.Keys, index);
            }


        }
        private static List<object> getRelationData(JsonRow row, List<string> keys, int index)
        {
            List<object> result = new List<object>();
            for (int i = 0; i < keys.Count; i++)
            {
                List<object> data = (List<object>)row.Cells[keys[i]].Value;
                if (index >= data.Count)
                {
                    return null;
                }
                result.Add(data[index]);
            }

            return result;
        }
        private static void executeIURelationCommand(SqlConnection conn,
            string cmdText,
            List<string> leftTableKeys, Dictionary<string, object> leftTableValues,
            List<string> rightTableKeys,
            List<object> rightTableValues)
        {
            if (conn == null || cmdText == null || leftTableKeys == null
                || leftTableValues == null || rightTableKeys == null
                || rightTableValues == null)
                return;

            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {

                for (int i = 0; i < leftTableKeys.Count; i++)
                {
                    cmd.Parameters.AddWithValue(leftTableKeys[i], leftTableValues[leftTableKeys[i]]);
                }
                for (int i = 0; i < rightTableKeys.Count; i++)
                {
                    cmd.Parameters.AddWithValue(rightTableKeys[i], rightTableValues[i]);
                }
                cmd.ExecuteNonQuery();


            }

        }

       
        private static string serialize(SqlDataReader reader)
        {
            var results = new List<Dictionary<string, object>>();
            var cols = new List<string>();
            for (var i = 0; i < reader.FieldCount; i++)
                cols.Add(reader.GetName(i));
            bool empty = true;
            while (reader.Read())
            {
                empty = false;
                results.Add(serializeRow(cols, reader));
            }
            if (!empty)
            {
                return JsonConvert.SerializeObject(results, Formatting.Indented);
            }
            else
            {
                return null;
            }
        }
        private static Dictionary<string, object> serializeRow(IEnumerable<string> cols,
                                                        SqlDataReader reader)
        {
            var result = new Dictionary<string, object>();
            foreach (var col in cols)
                result.Add(col, reader[col]);
            return result;
        }
        #endregion
    }
}
